﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class OrderDetailDA : IOrderDetailDA
    {
        private string ACTUAL_SHIP_DATE = "@ACTUAL_SHIP_DATE";
        private string ADJUSTED_QTY = "@ADJUSTED_QTY";
        private string ALLOCATE_RULE_ID = "@ALLOCATE_RULE_ID";
        private string ALLOCATE_STRATEGY_TYPE = "@ALLOCATE_STRATEGY_TYPE";
        private string ALLOCATED_QTY = "@ALLOCATED_QTY";
        private string ALT_SKU_ID = "@ALT_SKU_ID";
        private string BATCH_FLAG = "@BATCH_FLAG";
        private string BUYER_PO = "@BUYER_PO";
        private string CARTON_BREAK = "@CARTON_BREAK";
        private string CARTON_GROUP = "@CARTON_GROUP";
        private string CARTON_QTY_BREAK = "@CARTON_QTY_BREAK";
        private string COMPONENT_QTY = "@COMPONENT_QTY";
        private string COMPONENT_REFERENCE = "@COMPONENT_REFERENCE";
        private string CREATED_BY = "@CREATED_BY";
        private string CUBE = "@CUBE";
        private string DECLARATION_QTY = "@DECLARATION_QTY";
        private string EFFECTIVE_DATE = "@EFFECTIVE_DATE";
        private string EXPIRY_DATE = "@EXPIRY_DATE";
        private string EXTENDED_PRICE = "@EXTENDED_PRICE";
        private string EXTERN_LINE_ID = "@EXTERN_LINE_ID";
        private string EXTERN_ORDER_ID = "@EXTERN_ORDER_ID";
        private string EXTERNAL_LOC_SEQUENCE = "@EXTERNAL_LOC_SEQUENCE";
        private string EXTERNAL_LOT = "@EXTERNAL_LOT";
        private string GROSS_WGT = "@GROSS_WGT";
        private string HEIGHT = "@HEIGHT";
        private string IS_SUBSTITUTE = "@IS_SUBSTITUTE";
        private string LENGTH = "@LENGTH";
        private string LINE_ID = "@LINE_ID";
        private string LINE_TYPE = "@LINE_TYPE";
        private string LOT = "@LOT";
        private string LOT_ATTR01 = "@LOT_ATTR01";
        private string LOT_ATTR02 = "@LOT_ATTR02";
        private string LOT_ATTR03 = "@LOT_ATTR03";
        private string LOT_ATTR04 = "@LOT_ATTR04";
        private string LOT_ATTR05 = "@LOT_ATTR05";
        private string LOT_ATTR06 = "@LOT_ATTR06";
        private string LOT_ATTR07 = "@LOT_ATTR07";
        private string LOT_ATTR08 = "@LOT_ATTR08";
        private string LOT_ATTR09 = "@LOT_ATTR09";
        private string LOT_ATTR10 = "@LOT_ATTR10";
        private string LPN_ID = "@LPN_ID";
        private string MIN_SHIPPER_CENT = "@MIN_SHIPPER_CENT";
        private string NET_WGT = "@NET_WGT";
        private string NUMBER_LOST = "@NUMBER_LOST";
        private string OK_TO_SUBSTITUTE = "@OK_TO_SUBSTITUTE";
        private string OLD_ORDER_ID = "@OLD_ORDER_ID";
        private string OPEN_QTY = "@OPEN_QTY";
        private string OPP_REQUEST = "@OPP_REQUEST";
        private string ORDER_ID = "@ORDER_ID";
        private string ORIG_CASE_QTY = "@ORIG_CASE_QTY";
        private string ORIG_PALLET_QTY = "@ORIG_PALLET_QTY";
        private string ORIGINAL_LINE_ID = "@ORIGINAL_LINE_ID";
        private string ORIGINAL_QTY = "@ORIGINAL_QTY";
        private string ORIGINAL_SKU = "@ORIGINAL_SKU";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string PALLET_ID = "@PALLET_ID";
        private string PICK_CODE = "@PICK_CODE";
        private string PICKED_QTY = "@PICKED_QTY";
        private string PICKING_INSTRUCTIONS = "@PICKING_INSTRUCTIONS";
        private string PREALLOCATE_RULE_ID = "@PREALLOCATE_RULE_ID";
        private string PREALLOCATED_QTY = "@PREALLOCATED_QTY";
        private string PRODUCE_DATE = "@PRODUCE_DATE";
        private string PRODUCT_CUBE = "@PRODUCT_CUBE";
        private string PRODUCT_WEIGHT = "@PRODUCT_WEIGHT";
        private string QC_AUTO_ADJUST = "@QC_AUTO_ADJUST";
        private string QC_REQUIRED = "@QC_REQUIRED";
        private string QTY_INTRANSIT = "@QTY_INTRANSIT";
        private string RECEIPT_DATE = "@RECEIPT_DATE";
        private string RECEIVED_QTY = "@RECEIVED_QTY";
        private string REMARK = "@REMARK";
        private string ROTATION = "@ROTATION";
        private string SHELF_LIFE = "@SHELF_LIFE";
        private string SHIP_GROUP01 = "@SHIP_GROUP01";
        private string SHIP_GROUP02 = "@SHIP_GROUP02";
        private string SHIP_GROUP03 = "@SHIP_GROUP03";
        private string SHIPPED_QTY = "@SHIPPED_QTY";
        private string SKU_ID = "@SKU_ID";
        private string SKU_ROTATION = "@SKU_ROTATION";
        private string SQL_CHECK_ORDER_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_CHECK_ORDER_DETAIL_SKU_EXIST = "SELECT COUNT(1) FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID AND SKU_ID = @SKU_ID";
        private string SQL_DELETE_ORDER_DETAIL = " DELETE FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_ORDER_DETAIL_BY_ORDER_ID = " DELETE FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_INSERT_ORDER_DETAIL = " INSERT INTO @_@ORDER_DETAIL ( WH_ID, ORDER_ID, LINE_ID, EXTERN_ORDER_ID, EXTERN_LINE_ID, SKU_ID, ALT_SKU_ID, OWNER_ID, ORIGINAL_QTY, OPEN_QTY, SHIPPED_QTY, ADJUSTED_QTY, PREALLOCATED_QTY, ALLOCATED_QTY, PICKED_QTY, UOM_ID, PACK_ID, PICK_CODE, CARTON_GROUP, LPN_ID, STATUS, UNIT_PRICE, TAX01, TAX02, EXTENDED_PRICE, UPDATE_SOURCE, PRODUCT_WEIGHT, PRODUCT_CUBE, CARTON_BREAK, CARTON_QTY_BREAK, QC_REQUIRED, QC_AUTO_ADJUST, SHIP_GROUP01, SHIP_GROUP02, SHIP_GROUP03, TARIFF_ID, ORIGINAL_LINE_ID, ORIGINAL_SKU, BUYER_PO, ACTUAL_SHIP_DATE, IS_SUBSTITUTE, OK_TO_SUBSTITUTE, ALLOCATE_RULE_ID, PREALLOCATE_RULE_ID, ALLOCATE_STRATEGY_TYPE, SKU_ROTATION, SHELF_LIFE, ROTATION, SUB_FLAG, WORK_ORDER_ID, LINE_TYPE, COMPONENT_QTY, COMPONENT_REFERENCE, LOT, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, EXTERNAL_LOT,EFFECTIVE_DATE, PALLET_ID, ORIG_CASE_QTY, ORIG_PALLET_QTY, OPP_REQUEST, PICKING_INSTRUCTIONS, QTY_INTRANSIT, WP_RELEASED, EXTERNAL_LOC_SEQUENCE, MIN_SHIPPER_CENT, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, VENDOR_ID,RECEIPT_DATE,NUMBER_LOST,RECEIVED_QTY,CUBE,GROSS_WGT,NET_WGT,LENGTH,WIDTH,HEIGHT,BATCH_FLAG) VALUES ( @WH_ID, @ORDER_ID, @LINE_ID, @EXTERN_ORDER_ID, @EXTERN_LINE_ID, @SKU_ID, @ALT_SKU_ID, @OWNER_ID, @ORIGINAL_QTY, @OPEN_QTY, @SHIPPED_QTY, @ADJUSTED_QTY, @PREALLOCATED_QTY, @ALLOCATED_QTY, @PICKED_QTY, @UOM_ID, @PACK_ID, @PICK_CODE, @CARTON_GROUP, @LPN_ID, @STATUS, @UNIT_PRICE, @TAX01, @TAX02, @EXTENDED_PRICE, @UPDATE_SOURCE, @PRODUCT_WEIGHT, @PRODUCT_CUBE, @CARTON_BREAK, @CARTON_QTY_BREAK, @QC_REQUIRED, @QC_AUTO_ADJUST, @SHIP_GROUP01, @SHIP_GROUP02, @SHIP_GROUP03, @TARIFF_ID, @ORIGINAL_LINE_ID, @ORIGINAL_SKU, @BUYER_PO, @ACTUAL_SHIP_DATE, @IS_SUBSTITUTE, @OK_TO_SUBSTITUTE, @ALLOCATE_RULE_ID, @PREALLOCATE_RULE_ID, @ALLOCATE_STRATEGY_TYPE, @SKU_ROTATION, @SHELF_LIFE, @ROTATION, @SUB_FLAG, @WORK_ORDER_ID, @LINE_TYPE, @COMPONENT_QTY, @COMPONENT_REFERENCE, @LOT, @LOT_ATTR01, @LOT_ATTR02, @LOT_ATTR03, @LOT_ATTR04, @LOT_ATTR05, @LOT_ATTR06, @LOT_ATTR07, @LOT_ATTR08, @LOT_ATTR09, @LOT_ATTR10, @PRODUCE_DATE, @EXPIRY_DATE, @EXTERNAL_LOT, @EFFECTIVE_DATE, @PALLET_ID, @ORIG_CASE_QTY, @ORIG_PALLET_QTY, @OPP_REQUEST, @PICKING_INSTRUCTIONS, @QTY_INTRANSIT, @WP_RELEASED, @EXTERNAL_LOC_SEQUENCE, @MIN_SHIPPER_CENT, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@VENDOR_ID,@RECEIPT_DATE,@NUMBER_LOST,@RECEIVED_QTY,@CUBE,@GROSS_WGT,@NET_WGT,@LENGTH,@WIDTH,@HEIGHT,@BATCH_FLAG )  ";
        private string SQL_IS_COPY = " INSERT INTO @_@ORDER_DETAIL(WH_ID, ORDER_ID, LINE_ID, EXTERN_ORDER_ID, EXTERN_LINE_ID, SKU_ID, ALT_SKU_ID, OWNER_ID, ORIGINAL_QTY, OPEN_QTY, SHIPPED_QTY, ADJUSTED_QTY, PREALLOCATED_QTY, ALLOCATED_QTY, PICKED_QTY, UOM_ID, PACK_ID, PICK_CODE, CARTON_GROUP, LPN_ID, STATUS, UNIT_PRICE, TAX01, TAX02, EXTENDED_PRICE, UPDATE_SOURCE, PRODUCT_WEIGHT, PRODUCT_CUBE, CARTON_BREAK, CARTON_QTY_BREAK, QC_REQUIRED, QC_AUTO_ADJUST, SHIP_GROUP01, SHIP_GROUP02, SHIP_GROUP03, TARIFF_ID, ORIGINAL_LINE_ID, ORIGINAL_SKU, BUYER_PO, ACTUAL_SHIP_DATE, IS_SUBSTITUTE, OK_TO_SUBSTITUTE, ALLOCATE_RULE_ID, PREALLOCATE_RULE_ID, ALLOCATE_STRATEGY_TYPE, SKU_ROTATION, SHELF_LIFE, ROTATION, SUB_FLAG, WORK_ORDER_ID, LINE_TYPE, COMPONENT_QTY, COMPONENT_REFERENCE, LOT, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, EXTERNAL_LOT,EFFECTIVE_DATE, PALLET_ID, ORIG_CASE_QTY, ORIG_PALLET_QTY, OPP_REQUEST, PICKING_INSTRUCTIONS, QTY_INTRANSIT, WP_RELEASED, EXTERNAL_LOC_SEQUENCE, MIN_SHIPPER_CENT, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, VENDOR_ID,RECEIPT_DATE) SELECT WH_ID, '@ORDER_ID', LINE_ID, EXTERN_ORDER_ID, EXTERN_LINE_ID, SKU_ID, ALT_SKU_ID, OWNER_ID, ORIGINAL_QTY, 0, 0, 0, 0, 0, 0, UOM_ID, PACK_ID, PICK_CODE, CARTON_GROUP, LPN_ID, 'NEW', UNIT_PRICE, TAX01, TAX02, EXTENDED_PRICE, UPDATE_SOURCE, PRODUCT_WEIGHT, PRODUCT_CUBE, CARTON_BREAK, CARTON_QTY_BREAK, QC_REQUIRED, QC_AUTO_ADJUST, SHIP_GROUP01, SHIP_GROUP02, SHIP_GROUP03, TARIFF_ID, ORIGINAL_LINE_ID, ORIGINAL_SKU, BUYER_PO, ACTUAL_SHIP_DATE, IS_SUBSTITUTE, OK_TO_SUBSTITUTE, ALLOCATE_RULE_ID, PREALLOCATE_RULE_ID, ALLOCATE_STRATEGY_TYPE, SKU_ROTATION, SHELF_LIFE, ROTATION, SUB_FLAG, WORK_ORDER_ID, LINE_TYPE, COMPONENT_QTY, COMPONENT_REFERENCE, LOT, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, EXTERNAL_LOT,EFFECTIVE_DATE, PALLET_ID, ORIG_CASE_QTY, ORIG_PALLET_QTY, OPP_REQUEST, PICKING_INSTRUCTIONS, QTY_INTRANSIT, WP_RELEASED, EXTERNAL_LOC_SEQUENCE, MIN_SHIPPER_CENT, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, '@USER_ID', GETDATE(), '@USER_ID', GETDATE(), VENDOR_ID,RECEIPT_DATE FROM @_@ORDER_DETAIL WHERE ORDER_ID='@OLD_ORDER_ID'";
        private string SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT = " SELECT O.INTRANSIT_ID, OD.ROWID, OD.WH_ID, OD.ORDER_ID,  OD.LINE_ID, OD.EXTERN_ORDER_ID, OD.EXTERN_LINE_ID, OD.SKU_ID, OD.ALT_SKU_ID, OD.OWNER_ID,SKU.DESCR AS SKU_DESCR, OD.ORIGINAL_QTY, OD.OPEN_QTY, OD.SHIPPED_QTY, OD.ADJUSTED_QTY, OD.PREALLOCATED_QTY,   OD.ALLOCATED_QTY, OD.PICKED_QTY, OD.UOM_ID, OD.PACK_ID, OD.PICK_CODE, OD.CARTON_GROUP, OD.LPN_ID,T.[TEXT] AS STATUS_D, OD.STATUS, OD.UNIT_PRICE, OD.TAX01, OD.TAX02, OD.EXTENDED_PRICE, OD.UPDATE_SOURCE, OD.PRODUCT_WEIGHT, OD.PRODUCT_CUBE,   OD.CARTON_BREAK, OD.CARTON_QTY_BREAK, OD.QC_REQUIRED, OD.QC_AUTO_ADJUST, OD.SHIP_GROUP01, OD.SHIP_GROUP02, OD.SHIP_GROUP03, OD.TARIFF_ID, OD.ORIGINAL_LINE_ID, OD.ORIGINAL_SKU, OD.BUYER_PO, OD.IS_SUBSTITUTE, OD.ACTUAL_SHIP_DATE,  OD.OK_TO_SUBSTITUTE, OD.ALLOCATE_RULE_ID, OD.PREALLOCATE_RULE_ID, OD.ALLOCATE_STRATEGY_TYPE, OD.SKU_ROTATION, OD.SHELF_LIFE, OD.ROTATION, OD.SUB_FLAG, OD.WORK_ORDER_ID, OD.LINE_TYPE, OD.COMPONENT_QTY, OD.COMPONENT_REFERENCE, OD.LOT,  OD.LOT_ATTR01, OD.LOT_ATTR02, OD.LOT_ATTR03, OD.LOT_ATTR04, OD.LOT_ATTR05, OD.LOT_ATTR06, OD.LOT_ATTR07, OD.LOT_ATTR08,  OD.LOT_ATTR09, OD.LOT_ATTR10, OD.PRODUCE_DATE, OD.EXPIRY_DATE, OD.RECEIPT_DATE, OD.EXTERNAL_LOT, OD.EFFECTIVE_DATE, OD.PALLET_ID,     OD.ORIG_CASE_QTY, OD.ORIG_PALLET_QTY, OD.OPP_REQUEST,  OD.PICKING_INSTRUCTIONS, OD.QTY_INTRANSIT, OD.WP_RELEASED,  OD.EXTERNAL_LOC_SEQUENCE, OD.MIN_SHIPPER_CENT, OD.UDF1, OD.UDF2,  OD.UDF3, OD.UDF4, OD.UDF5, OD.UDF6, OD.UDF8,   OD.UDF7, OD.UDF9, OD.UDF10, OD.REMARK, OD.TS,  OD.CREATED_BY, OD.CREATED_DATE, OD.UPDATED_BY,  OD.UPDATED_DATE,OD.VENDOR_ID,OD.DECLARATION_QTY,OD.NUMBER_LOST,OD.RECEIVED_QTY,OD.CUBE,OD.GROSS_WGT,OD.NET_WGT,OD.LENGTH,OD.WIDTH,OD.HEIGHT,OD.BATCH_FLAG,SKU.SKU_STYLE  FROM @_@ORDER_DETAIL OD INNER JOIN @_@ORDERS O ON OD.ORDER_ID = O.ORDER_ID LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = OD.OWNER_ID AND SKU.SKU_ID = OD.SKU_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = OD.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+OD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID";
        private string SQL_SELECT_ALL_ORDER_DETAILA = " SELECT OD.ROWID, OD.WH_ID, OD.ORDER_ID,  OD.LINE_ID, OD.EXTERN_ORDER_ID, OD.EXTERN_LINE_ID, OD.SKU_ID, OD.ALT_SKU_ID, OD.OWNER_ID,SKU.DESCR AS SKU_DESCR, OD.ORIGINAL_QTY, OD.OPEN_QTY, OD.SHIPPED_QTY, OD.ADJUSTED_QTY, OD.PREALLOCATED_QTY,   OD.ALLOCATED_QTY, OD.PICKED_QTY, OD.UOM_ID, OD.PACK_ID, OD.PICK_CODE, OD.CARTON_GROUP, OD.LPN_ID,T.[TEXT] AS STATUS_D, OD.STATUS, OD.UNIT_PRICE, OD.TAX01, OD.TAX02, OD.EXTENDED_PRICE, OD.UPDATE_SOURCE, OD.PRODUCT_WEIGHT, OD.PRODUCT_CUBE,   OD.CARTON_BREAK, OD.CARTON_QTY_BREAK, OD.QC_REQUIRED, OD.QC_AUTO_ADJUST, OD.SHIP_GROUP01, OD.SHIP_GROUP02, OD.SHIP_GROUP03, OD.TARIFF_ID, OD.ORIGINAL_LINE_ID, OD.ORIGINAL_SKU, OD.BUYER_PO, OD.IS_SUBSTITUTE, OD.ACTUAL_SHIP_DATE,  OD.OK_TO_SUBSTITUTE, OD.ALLOCATE_RULE_ID, OD.PREALLOCATE_RULE_ID, OD.ALLOCATE_STRATEGY_TYPE, OD.SKU_ROTATION, OD.SHELF_LIFE, OD.ROTATION, OD.SUB_FLAG, OD.WORK_ORDER_ID, OD.LINE_TYPE, OD.COMPONENT_QTY, OD.COMPONENT_REFERENCE, OD.LOT,  OD.LOT_ATTR01, OD.LOT_ATTR02, OD.LOT_ATTR03, OD.LOT_ATTR04, OD.LOT_ATTR05, OD.LOT_ATTR06, OD.LOT_ATTR07, OD.LOT_ATTR08,  OD.LOT_ATTR09, OD.LOT_ATTR10, OD.PRODUCE_DATE, OD.EXPIRY_DATE, OD.RECEIPT_DATE, OD.EXTERNAL_LOT, OD.EFFECTIVE_DATE, OD.PALLET_ID,     OD.ORIG_CASE_QTY, OD.ORIG_PALLET_QTY, OD.OPP_REQUEST,  OD.PICKING_INSTRUCTIONS, OD.QTY_INTRANSIT, OD.WP_RELEASED,  OD.EXTERNAL_LOC_SEQUENCE, OD.MIN_SHIPPER_CENT, OD.UDF1, OD.UDF2,  OD.UDF3, OD.UDF4, OD.UDF5, OD.UDF6, OD.UDF8,   OD.UDF7, OD.UDF9, OD.UDF10, OD.REMARK, OD.TS,  OD.CREATED_BY, OD.CREATED_DATE, OD.UPDATED_BY,  OD.UPDATED_DATE,OD.VENDOR_ID,OD.DECLARATION_QTY,OD.NUMBER_LOST,OD.RECEIVED_QTY,OD.CUBE,OD.GROSS_WGT,OD.NET_WGT,OD.LENGTH,OD.WIDTH,OD.HEIGHT,OD.BATCH_FLAG,SKU.SKU_STYLE  FROM @_@ORDER_DETAIL OD  LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = OD.OWNER_ID AND SKU.SKU_ID = OD.SKU_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = OD.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+OD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_ORDER_DETAILA_FOR_CONTAINER = " SELECT  OD.ROWID, OD.WH_ID, OD.ORDER_ID, OD.LINE_ID, OD.EXTERN_ORDER_ID, OD.EXTERN_LINE_ID, OD.SKU_ID, OD.ALT_SKU_ID, OD.OWNER_ID, SKU.DESCR AS SKU_DESCR,OD.ORIGINAL_QTY, OD.OPEN_QTY, OD.SHIPPED_QTY, OD.ADJUSTED_QTY, OD.PREALLOCATED_QTY, OD.ALLOCATED_QTY, OD.PICKED_QTY, OD.UOM_ID, OD.PACK_ID, OD.PICK_CODE, OD.CARTON_GROUP, OD.LPN_ID, OD.STATUS, OD.UNIT_PRICE, OD.TAX01, OD.TAX02, OD.EXTENDED_PRICE, OD.UPDATE_SOURCE, OD.PRODUCT_WEIGHT, OD.PRODUCT_CUBE, OD.CARTON_BREAK, OD.CARTON_QTY_BREAK, OD.QC_REQUIRED, OD.QC_AUTO_ADJUST, OD.SHIP_GROUP01, OD.SHIP_GROUP02, OD.SHIP_GROUP03, OD.TARIFF_ID, OD.ORIGINAL_LINE_ID, OD.ORIGINAL_SKU, OD.BUYER_PO, OD.ACTUAL_SHIP_DATE, OD.IS_SUBSTITUTE, OD.OK_TO_SUBSTITUTE, OD.ALLOCATE_RULE_ID, OD.PREALLOCATE_RULE_ID, OD.ALLOCATE_STRATEGY_TYPE, OD.SKU_ROTATION, OD.SHELF_LIFE, OD.ROTATION, OD.SUB_FLAG, OD.WORK_ORDER_ID, OD.LINE_TYPE, OD.COMPONENT_QTY, OD.COMPONENT_REFERENCE, OD.LOT, OD.LOT_ATTR01, OD.LOT_ATTR02, OD.LOT_ATTR03, OD.LOT_ATTR04, OD.LOT_ATTR05, OD.LOT_ATTR06, OD.LOT_ATTR07, OD.LOT_ATTR08, OD.LOT_ATTR09, OD.LOT_ATTR10, OD.PRODUCE_DATE, OD.EXPIRY_DATE,OD.RECEIPT_DATE, OD.EXTERNAL_LOT, OD.EFFECTIVE_DATE, OD.PALLET_ID, OD.ORIG_CASE_QTY, OD.ORIG_PALLET_QTY, OD.OPP_REQUEST, OD.PICKING_INSTRUCTIONS, OD.QTY_INTRANSIT, OD.WP_RELEASED, OD.EXTERNAL_LOC_SEQUENCE, OD.MIN_SHIPPER_CENT, OD.UDF1, OD.UDF2, OD.UDF3, OD.UDF4, OD.UDF5, OD.UDF6, OD.UDF7, OD.UDF8, OD.UDF9, OD.UDF10, OD.REMARK, OD.TS, OD.CREATED_BY, OD.CREATED_DATE, OD.UPDATED_BY, OD.UPDATED_DATE,OD.BATCH_FLAG, (SELECT ISNULL(SUM(SC.QTY),0) FROM  @_@SHIPMENT_CONTAINER SC WHERE SC.ORDER_ID =@ORDER_ID  AND  SC.ORDER_LINE_ID = LINE_ID AND  CONTAINER_ID=@CONTAINER_ID )  AS ENCASED_QTY , (SELECT PICKED_QTY-ISNULL(SUM(SC.QTY),0) FROM  @_@SHIPMENT_CONTAINER SC WHERE SC.ORDER_ID =@ORDER_ID  AND  SC.ORDER_LINE_ID = LINE_ID )  AS UNENCASED_QTY ,VENDOR_ID ,T.[TEXT] AS STATUS_D,OD.DECLARATION_QTY,OD.NUMBER_LOST,OD.RECEIVED_QTY,OD.CUBE,OD.GROSS_WGT,OD.NET_WGT,OD.LENGTH,OD.WIDTH,OD.HEIGHT,SKU.SKU_STYLE  FROM  @_@ORDER_DETAIL OD LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = OD.OWNER_ID AND SKU.SKU_ID = OD.SKU_ID   LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = OD.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus'  LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+OD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE  INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_ORDER_DETAILA_WAVE_TEMPLATE = " SELECT  @MAX_ORDERS OD.ORDER_ID, OD.STATUS,OD.LINE_ID, OD.OWNER_ID,O.CONSIGNEE_ID,O.TRANSPORTATION_MODE,O.CARRIER_ID  FROM @_@ORDER_DETAIL OD  LEFT JOIN @_@ORDERS O  ON OD.ORDER_ID = O.ORDER_ID   LEFT JOIN (SELECT ORDER_ID,  COUNT(*) AS TOTALCOUNT FROM @_@ORDER_DETAIL GROUP BY  ORDER_ID ) ORDERCOUNT  ON O.ORDER_ID=ORDERCOUNT.ORDER_ID LEFT JOIN (SELECT  ORDER_ID, SUM(S.NET_WGT * OD1.ORIGINAL_QTY) AS TOTALWGT, SUM(S.CUBE*OD1.ORIGINAL_QTY) AS TOTALCUBE, SUM(OD1.ORIGINAL_QTY) AS TOTALQTY,COUNT(DISTINCT ISNULL(OD1.OWNER_ID,'') + '' + ISNULL(OD1.SKU_ID,'')) AS TOTALSKUCOUNT FROM @_@ORDER_DETAIL OD1 LEFT JOIN @_@SKU S  ON OD1.OWNER_ID = S.OWNER_ID AND OD1.SKU_ID = S.SKU_ID   GROUP BY ORDER_ID  )  ORDERSKUCOUNT  ON O.ORDER_ID=ORDERSKUCOUNT.ORDER_ID  LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = OD.OWNER_ID AND SKU.SKU_ID = OD.SKU_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = OD.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+OD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  LEFT JOIN  @_@WAVE_DETAIL WD  ON O.ORDER_ID=WD.ORDER_ID";
        private string SQL_SELECT_GET_ORDER_LINE_ID_FOR_SHIPPING = " SELECT DISTINCT LINE_ID FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID AND STATUS IN ('PARTPICKED','PICKED','REVIEWED','PARTSHIPPED','Loaded') ";
        private string SQL_SELECT_INV_QTY_BY_ALLOCATE_STRATEGY = " SELECT INV_LOT_LOC_LPN.LOC, INV_LOT_LOC_LPN.LPN_ID, INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY - INV_LOT_LOC_LPN.EXPECTED_QTY - INV_LOT_LOC_LPN.PICKINPROCESS_QTY - INV_LOT_LOC_LPN.PENDING_MOVE_IN  AS QTYAVAILABLE  FROM   @_@INV_LOT_LOC_LPN INV_LOT_LOC_LPN  LEFT JOIN @_@LOCATION LOCATION ON INV_LOT_LOC_LPN.LOC = LOCATION.LOC LEFT JOIN @_@LPN LPN ON INV_LOT_LOC_LPN.LPN_ID = LPN.LPN_ID WHERE   INV_LOT_LOC_LPN.LOT = @LOT   AND LOCATION.LOC_FLAG <>'HOLD' AND  LOCATION.LOC_FLAG <> 'DAMAGE' AND  (INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY - INV_LOT_LOC_LPN.EXPECTED_QTY - INV_LOT_LOC_LPN.PICKINPROCESS_QTY - INV_LOT_LOC_LPN.PENDING_MOVE_IN) > '0'  AND  LOCATION.STATUS <> 'HOLD' AND  (LPN.STATUS <> 'HOLD' OR LPN.STATUS IS NULL OR LPN.STATUS = '')  ";
        private string SQL_UPDATE_ACTUAL_SHIP_DATE_BY_ORDER_ID_LINE_ID = " UPDATE @_@ORDER_DETAIL  SET ACTUAL_SHIP_DATE=@ACTUAL_SHIP_DATE , UPDATED_DATE=@UPDATED_DATE,UPDATED_BY=@UPDATED_BY WHERE ORDER_ID=@ORDER_ID AND LINE_ID=@LINE_ID";
        private string SQL_UPDATE_ORDER_DETAIL = " UPDATE @_@ORDER_DETAIL SET WH_ID = @WH_ID, EXTERN_ORDER_ID = @EXTERN_ORDER_ID, EXTERN_LINE_ID = @EXTERN_LINE_ID, SKU_ID = @SKU_ID, ALT_SKU_ID = @ALT_SKU_ID, OWNER_ID = @OWNER_ID, ORIGINAL_QTY = @ORIGINAL_QTY, OPEN_QTY = @OPEN_QTY, SHIPPED_QTY = @SHIPPED_QTY, ADJUSTED_QTY = @ADJUSTED_QTY, PREALLOCATED_QTY = @PREALLOCATED_QTY, ALLOCATED_QTY = @ALLOCATED_QTY, PICKED_QTY = @PICKED_QTY, UOM_ID = @UOM_ID, PACK_ID = @PACK_ID, PICK_CODE = @PICK_CODE, CARTON_GROUP = @CARTON_GROUP, LPN_ID = @LPN_ID, STATUS = @STATUS, UNIT_PRICE = @UNIT_PRICE, TAX01 = @TAX01, TAX02 = @TAX02, EXTENDED_PRICE = @EXTENDED_PRICE, UPDATE_SOURCE = @UPDATE_SOURCE, PRODUCT_WEIGHT = @PRODUCT_WEIGHT, PRODUCT_CUBE = @PRODUCT_CUBE, CARTON_BREAK = @CARTON_BREAK, CARTON_QTY_BREAK = @CARTON_QTY_BREAK, QC_REQUIRED = @QC_REQUIRED, QC_AUTO_ADJUST = @QC_AUTO_ADJUST, SHIP_GROUP01 = @SHIP_GROUP01, SHIP_GROUP02 = @SHIP_GROUP02, SHIP_GROUP03 = @SHIP_GROUP03, TARIFF_ID = @TARIFF_ID, ORIGINAL_LINE_ID = @ORIGINAL_LINE_ID, ORIGINAL_SKU = @ORIGINAL_SKU, BUYER_PO = @BUYER_PO, ACTUAL_SHIP_DATE = @ACTUAL_SHIP_DATE, IS_SUBSTITUTE = @IS_SUBSTITUTE, OK_TO_SUBSTITUTE = @OK_TO_SUBSTITUTE, ALLOCATE_RULE_ID = @ALLOCATE_RULE_ID, PREALLOCATE_RULE_ID = @PREALLOCATE_RULE_ID, ALLOCATE_STRATEGY_TYPE = @ALLOCATE_STRATEGY_TYPE, SKU_ROTATION = @SKU_ROTATION, SHELF_LIFE = @SHELF_LIFE, ROTATION = @ROTATION, SUB_FLAG = @SUB_FLAG, WORK_ORDER_ID = @WORK_ORDER_ID, LINE_TYPE = @LINE_TYPE, COMPONENT_QTY = @COMPONENT_QTY, COMPONENT_REFERENCE = @COMPONENT_REFERENCE, LOT = @LOT, LOT_ATTR01 = @LOT_ATTR01, LOT_ATTR02 = @LOT_ATTR02, LOT_ATTR03 = @LOT_ATTR03, LOT_ATTR04 = @LOT_ATTR04, LOT_ATTR05 = @LOT_ATTR05, LOT_ATTR06 = @LOT_ATTR06, LOT_ATTR07 = @LOT_ATTR07, LOT_ATTR08 = @LOT_ATTR08, LOT_ATTR09 = @LOT_ATTR09, LOT_ATTR10 = @LOT_ATTR10, PRODUCE_DATE = @PRODUCE_DATE, EXPIRY_DATE = @EXPIRY_DATE, EXTERNAL_LOT = @EXTERNAL_LOT, EFFECTIVE_DATE = @EFFECTIVE_DATE, PALLET_ID = @PALLET_ID, ORIG_CASE_QTY = @ORIG_CASE_QTY, ORIG_PALLET_QTY = @ORIG_PALLET_QTY, OPP_REQUEST = @OPP_REQUEST, PICKING_INSTRUCTIONS = @PICKING_INSTRUCTIONS, QTY_INTRANSIT = @QTY_INTRANSIT, WP_RELEASED = @WP_RELEASED, EXTERNAL_LOC_SEQUENCE = @EXTERNAL_LOC_SEQUENCE, MIN_SHIPPER_CENT = @MIN_SHIPPER_CENT, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),VENDOR_ID = @VENDOR_ID,RECEIPT_DATE = @RECEIPT_DATE,NUMBER_LOST=@NUMBER_LOST,RECEIVED_QTY=@RECEIVED_QTY,CUBE=@CUBE,GROSS_WGT=@GROSS_WGT,NET_WGT=@NET_WGT,LENGTH=@LENGTH,WIDTH=@WIDTH,HEIGHT=@HEIGHT,BATCH_FLAG=@BATCH_FLAG WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID  ";
        private string SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY = " UPDATE @_@ORDER_DETAIL SET ALLOCATED_QTY = ALLOCATED_QTY + @ALLOCATED_QTY, PREALLOCATED_QTY = PREALLOCATED_QTY - @ALLOCATED_QTY,   STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY_ONLY = " UPDATE @_@ORDER_DETAIL SET ALLOCATED_QTY = ALLOCATED_QTY + @ALLOCATED_QTY, OPEN_QTY = ORIGINAL_QTY - SHIPPED_QTY, STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_BATCH_FLAG = " UPDATE @_@ORDER_DETAIL SET BATCH_FLAG = @BATCH_FLAG WHERE ORDER_ID = @ORDER_ID AND LINE_ID =@LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_PICKED_QTY = " UPDATE @_@ORDER_DETAIL SET PICKED_QTY = PICKED_QTY + @PICKED_QTY, ALLOCATED_QTY = ALLOCATED_QTY - @PICKED_QTY, STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_PREALLOCATE_QTY = " UPDATE @_@ORDER_DETAIL SET PREALLOCATED_QTY = PREALLOCATED_QTY + @PREALLOCATED_QTY , OPEN_QTY = ORIGINAL_QTY - SHIPPED_QTY,  STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_REVERT_ALLOCATE_QTY = " UPDATE @_@ORDER_DETAIL SET ALLOCATED_QTY = ALLOCATED_QTY - @ALLOCATED_QTY,PREALLOCATED_QTY = PREALLOCATED_QTY + @ALLOCATED_QTY ,  STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_SHIPPED_QTY = " UPDATE @_@ORDER_DETAIL SET SHIPPED_QTY = SHIPPED_QTY + @SHIPPED_QTY, PICKED_QTY = PICKED_QTY - @SHIPPED_QTY, STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),ACTUAL_SHIP_DATE=@ACTUAL_SHIP_DATE WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_STATUS = " UPDATE @_@ORDER_DETAIL SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_STATUS_BY_ORDER_ID = " UPDATE @_@ORDER_DETAIL SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_STATUS_BY_ORDER_ID_AND_LINE_ID = " UPDATE @_@ORDER_DETAIL SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID";
        private string SQL_UPDATE_ORDER_DETAIL_UDF = " UPDATE @_@ORDER_DETAIL SET UDF1=@UDF1 , UDF2=@UDF2 , REMARK=@REMARK , UPDATED_DATE=GETDATE(), UPDATED_BY=@UPDATED_BY WHERE ORDER_ID=@ORDER_ID AND LINE_ID=@LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_UN_SHIPPED_QTY = " UPDATE @_@ORDER_DETAIL SET SHIPPED_QTY = SHIPPED_QTY - @SHIPPED_QTY, STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_UN_SHIPPED_QTY_TO_PICKED = " UPDATE @_@ORDER_DETAIL SET SHIPPED_QTY = SHIPPED_QTY - @SHIPPED_QTY,PICKED_QTY=PICKED_QTY+@SHIPPED_QTY, STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string STATUS = "@STATUS";
        private string SUB_FLAG = "@SUB_FLAG";
        private string TARIFF_ID = "@TARIFF_ID";
        private string TAX01 = "@TAX01";
        private string TAX02 = "@TAX02";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UNIT_PRICE = "@UNIT_PRICE";
        private string UOM_ID = "@UOM_ID";
        private string UPDATE_SOURCE = "@UPDATE_SOURCE";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VENDOR_ID = "@VENDOR_ID";
        private string WH_ID = "@WH_ID";
        private string WIDTH = "@WIDTH";
        private string WORK_ORDER_ID = "@WORK_ORDER_ID";
        private string WP_RELEASED = "@WP_RELEASED";

        public bool CheckOrderDetailIDUnique(string orderID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_ORDER_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckOrderDetailIDUnique(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_ORDER_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckOrderDetailSkuExist(string orderID, string skuID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@SKU_ID", skuID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_ORDER_DETAIL_SKU_EXIST.Replace("@_@", whLoginID), paras).ToString() != "0");
        }

        public bool CheckOrderDetailSkuExist(DataBase dataBase, DbTransaction tran, string orderID, string skuID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@SKU_ID", skuID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_ORDER_DETAIL_SKU_EXIST.Replace("@_@", whLoginID), paras).ToString() != "0");
        }

        public bool ConfirmPicked(PickDetailInfo pickDetailInfo, string whLoginID)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("UPDATE @_@TASK_DETAIL SET [STATUS] = 'Completed', UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID;");
                sb.AppendLine("UPDATE @_@PICK_DETAIL SET [STATUS] = 'Picked', TO_LOC = @TO_LOC, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID;");
                sb.AppendLine("UPDATE @_@INV_LOT SET PICKED_QTY = PICKED_QTY + @QTY, ALLOCATED_QTY = ALLOCATED_QTY - @QTY, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE LOT = @LOT ;");
                sb.AppendLine("UPDATE @_@INV_LOT_LOC_LPN SET QTY = QTY - @QTY, ALLOCATED_QTY = ALLOCATED_QTY - @QTY, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @FROM_LOC AND LPN_ID = @LPN_ID;");
                sb.AppendLine("UPDATE @_@INV_LOT_LOC_LPN SET QTY = QTY + @QTY, PICKED_QTY = PICKED_QTY + @QTY, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @TO_LOC AND LPN_ID = @LPN_ID;");
                sb.AppendLine("UPDATE @_@INV_SKU_LOC SET QTY = QTY - @QTY, ALLOCATED_QTY = ALLOCATED_QTY - @QTY, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE LOC = @FROM_LOC AND OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID;");
                sb.AppendLine("UPDATE @_@INV_SKU_LOC SET QTY = QTY + @QTY, PICKED_QTY = PICKED_QTY + @QTY, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE LOC = @TO_LOC AND OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID;");
                sb.AppendLine("UPDATE @_@ORDER_DETAIL SET ALLOCATED_QTY = ALLOCATED_QTY - @QTY, PICKED_QTY = PICKED_QTY + @QTY, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID;");
                string sql = sb.ToString().Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", pickDetailInfo.OrderID), new SqlParameter("@LINE_ID", pickDetailInfo.OrderLineID), new SqlParameter("@PICK_DETAIL_ID", pickDetailInfo.PickDetailID), new SqlParameter("@QTY", pickDetailInfo.Qty), new SqlParameter("@OWNER_ID", pickDetailInfo.OwnerID), new SqlParameter("@SKU_ID", pickDetailInfo.SkuID), new SqlParameter("@LOT", pickDetailInfo.Lot), new SqlParameter("@FROM_LOC", pickDetailInfo.Loc), new SqlParameter("@TO_LOC", pickDetailInfo.ToLoc), new SqlParameter("@LPN_ID", pickDetailInfo.LpnID), new SqlParameter("@UPDATED_BY", pickDetailInfo.UpdatedBy) };
                return (DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) > 0);
            }
            catch (Exception)
            {
                return false;
            }
        }

        public int DeleteOrderDetail(List<string> orderID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < orderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDER_DETAIL_BY_ORDER_ID.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteOrderDetail(List<string> orderDetailID, List<string> lineIDs, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < orderDetailID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderDetailID[j]), new SqlParameter("LINE_ID", lineIDs[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDER_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteOrderDetail(DataBase dataBase, DbTransaction tran, List<string> orderID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < orderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDER_DETAIL_BY_ORDER_ID.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteOrderDetail(DataBase dataBase, DbTransaction tran, List<string> orderDetailID, List<string> lineIDs, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < orderDetailID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderDetailID[j]), new SqlParameter("LINE_ID", lineIDs[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDER_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteOrderDetailByOrigOrderIDAndLineID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string whLoginID)
        {
            string sql = "DELETE FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID".Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
        }

        public DataSet GetAllOrderDetail(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllOrderDetail(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public DataSet GetInvQty4AllocateByStrategy(string strategy, string lot, string packQty, string ownerID, string skuID, string whLoginID)
        {
            string sql = this.SQL_SELECT_INV_QTY_BY_ALLOCATE_STRATEGY.Replace("@_@", whLoginID);
            SqlParameter[] paras = null;
            string CS40001 = strategy;
            if (CS40001 != null)
            {
                if (!(CS40001 == "10"))
                {
                    if (CS40001 == "20")
                    {
                        sql = sql + " ORDER BY ABS(INV_LOT_LOC_LPN.QTY-INV_LOT_LOC_LPN.ALLOCATED_QTY), LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                    else if (CS40001 == "30")
                    {
                        sql = sql + " ORDER BY (INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY) DESC, LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                    else if (CS40001 == "40")
                    {
                        sql = sql + " ORDER BY (INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY) ASC, LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                }
                else
                {
                    sql = (sql + " AND LOCATION.LOC IN (SELECT DISTINCT LOC FROM @_@INV_SKU_LOC WHERE OWNER_ID=@OWNER_ID AND SKU_ID=@SKU_ID AND (LOCATION_TYPE='CASE' OR LOCATION_TYPE='PIECE') ) ").Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty), new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
                }
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetInvQty4AllocateByStrategy(DataBase dataBase, DbTransaction tran, string strategy, string lot, string packQty, string ownerID, string skuID, string whLoginID)
        {
            string sql = this.SQL_SELECT_INV_QTY_BY_ALLOCATE_STRATEGY.Replace("@_@", whLoginID);
            SqlParameter[] paras = null;
            string CS40001 = strategy;
            if (CS40001 != null)
            {
                if (!(CS40001 == "10"))
                {
                    if (CS40001 == "20")
                    {
                        sql = sql + " ORDER BY ABS(INV_LOT_LOC_LPN.QTY-INV_LOT_LOC_LPN.ALLOCATED_QTY), LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                    else if (CS40001 == "30")
                    {
                        sql = sql + " ORDER BY (INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY) DESC, LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                    else if (CS40001 == "40")
                    {
                        sql = sql + " ORDER BY (INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY) ASC, LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                }
                else
                {
                    sql = (sql + " AND LOCATION.LOC IN (SELECT DISTINCT LOC FROM @_@INV_SKU_LOC WHERE OWNER_ID=@OWNER_ID AND SKU_ID=@SKU_ID AND (LOCATION_TYPE='CASE' OR LOCATION_TYPE='PIECE') ) ").Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty), new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
                }
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public int GetMaxOrderDetailLineID(string OrderID, string whLoginID)
        {
            string strSql = "SELECT MAX(LINE_ID) FROM @_@ORDER_DETAIL WHERE ORDER_ID=@ORDER_ID".Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", OrderID) };
            return (int) DBHelper.ExecuteScalar(CommandType.Text, strSql, paras);
        }

        public int GetMaxOrderDetailLineID(DataBase dataBase, DbTransaction tran, string OrderID, string whLoginID)
        {
            string strSql = "SELECT MAX(LINE_ID) FROM @_@ORDER_DETAIL WHERE ORDER_ID=@ORDER_ID".Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", OrderID) };
            return (int) dataBase.ExecuteScalar(tran, CommandType.Text, strSql, paras);
        }

        public List<BillingOrderDetailInfo> GetOrderBillingOrderDetailListInfo(string orderID, string billingObject, string whLoginID)
        {
            string sql = string.Empty;
            if (billingObject == "Owner")
            {
                sql = "  SELECT 'OutBound' AS BILLING_TYPE,R.ORDER_ID AS DOC_ID,R.EXTERN_ORDER_ID AS EXTERNAL_DOC_ID,R.OWNER_ID,OD.SKU_ID,'Owner' AS BILLING_OBJECT,R.OWNER_ID AS BILLING_OBJECT_ID,O.COMPANY AS BILLING_OBJECT_NAME,SUM(OD.SHIPPED_QTY) AS QTY,  (SELECT SUM(ISNULL(IP.[CASE],0)) AS [CASE] FROM @_@INV_PALLET IP WHERE IP.DOC_ID = R.ORDER_ID AND IP.DOC_TYPE = 'SO' AND IP.OWNER_ID = OD.OWNER_ID AND IP.SKU_ID = OD.SKU_ID )  AS [CASE],  (SELECT SUM(ISNULL(IP.BILLING_PALLET,0)) AS PALLET FROM @_@INV_PALLET IP WHERE IP.DOC_ID = R.ORDER_ID AND IP.DOC_TYPE = 'SO' AND IP.OWNER_ID = OD.OWNER_ID AND IP.SKU_ID = OD.SKU_ID )  AS [PALLET]   FROM @_@ORDER_DETAIL OD   LEFT JOIN @_@ORDERS R ON R.ORDER_ID = OD.ORDER_ID   LEFT JOIN @_@OWNER O ON O.OWNER_ID = R.OWNER_ID   WHERE ISNULL(R.OWNER_ID,'') <> '' AND  R.ORDER_ID = @ORDER_ID   GROUP BY R.ORDER_ID,R.EXTERN_ORDER_ID,R.OWNER_ID,OD.SKU_ID,OD.OWNER_ID,O.COMPANY";
            }
            else if (billingObject == "Customer")
            {
                sql = "SELECT 'OutBound' AS BILLING_TYPE,R.ORDER_ID AS DOC_ID,R.EXTERN_ORDER_ID AS EXTERNAL_DOC_ID,R.OWNER_ID,OD.SKU_ID,'Customer' AS BILLING_OBJECT,O.CUSTOMER_ID AS BILLING_OBJECT_ID,O.COMPANY AS BILLING_OBJECT_NAME,SUM(OD.SHIPPED_QTY) AS QTY,  (SELECT SUM(ISNULL(IP.[CASE],0)) AS [CASE] FROM @_@INV_PALLET IP WHERE IP.DOC_ID = R.ORDER_ID AND IP.DOC_TYPE = 'SO' AND IP.OWNER_ID = OD.OWNER_ID AND IP.SKU_ID = OD.SKU_ID )  AS [CASE],  (SELECT SUM(ISNULL(IP.BILLING_PALLET,0)) AS PALLET FROM @_@INV_PALLET IP WHERE IP.DOC_ID = R.ORDER_ID AND IP.DOC_TYPE = 'SO' AND IP.OWNER_ID = OD.OWNER_ID AND IP.SKU_ID = OD.SKU_ID )  AS [PALLET]   FROM @_@ORDER_DETAIL OD   LEFT JOIN @_@ORDERS R ON R.ORDER_ID = OD.ORDER_ID  LEFT JOIN @_@CUSTOMER O ON O.CUSTOMER_ID = R.CONSIGNEE_ID   WHERE ISNULL(O.CUSTOMER_ID,'') <> '' AND  R.ORDER_ID = @ORDER_ID  GROUP BY R.ORDER_ID,R.EXTERN_ORDER_ID,R.OWNER_ID,O.CUSTOMER_ID,OD.SKU_ID,OD.OWNER_ID,O.COMPANY ";
            }
            else if (billingObject == "Vendor")
            {
                sql = "SELECT 'OutBound' AS BILLING_TYPE,R.ORDER_ID AS DOC_ID,R.EXTERN_ORDER_ID AS EXTERNAL_DOC_ID,R.OWNER_ID,OD.SKU_ID,'Vendor' AS BILLING_OBJECT,LA.VENDOR_ID AS BILLING_OBJECT_ID,O.COMPANY AS BILLING_OBJECT_NAME,SUM(OD.QTY) AS QTY ,0  AS [CASE],0  AS [PALLET]   FROM  @_@PICK_DETAIL OD   LEFT JOIN @_@ORDERS R ON R.ORDER_ID = OD.ORDER_ID  LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.LOT = OD.LOT  LEFT JOIN @_@VENDOR O ON O.VENDOR_ID = LA.VENDOR_ID   WHERE ISNULL(LA.VENDOR_ID,'') <> '' AND  R.ORDER_ID = @ORDER_ID  GROUP BY R.ORDER_ID,R.EXTERN_ORDER_ID,R.OWNER_ID,LA.VENDOR_ID,OD.SKU_ID,OD.OWNER_ID,O.COMPANY ";
            }
            else if (billingObject == "Carrier")
            {
                sql = "SELECT 'OutBound' AS BILLING_TYPE,R.ORDER_ID AS DOC_ID,R.EXTERN_ORDER_ID AS EXTERNAL_DOC_ID,R.OWNER_ID,OD.SKU_ID,'Carrier' AS BILLING_OBJECT,O.CARRIER_ID AS BILLING_OBJECT_ID,O.COMPANY AS BILLING_OBJECT_NAME,SUM(OD.SHIPPED_QTY) AS QTY,  (SELECT SUM(ISNULL(IP.[CASE],0)) AS [CASE] FROM @_@INV_PALLET IP WHERE IP.DOC_ID = R.ORDER_ID AND IP.DOC_TYPE = 'SO' AND IP.OWNER_ID = OD.OWNER_ID AND IP.SKU_ID = OD.SKU_ID )  AS [CASE],  (SELECT SUM(ISNULL(IP.BILLING_PALLET,0)) AS PALLET FROM @_@INV_PALLET IP WHERE IP.DOC_ID = R.ORDER_ID AND IP.DOC_TYPE = 'SO' AND IP.OWNER_ID = OD.OWNER_ID AND IP.SKU_ID = OD.SKU_ID )  AS [PALLET]   FROM @_@ORDER_DETAIL OD   LEFT JOIN @_@ORDERS R ON R.ORDER_ID = OD.ORDER_ID  LEFT JOIN @_@CARRIER O ON O.CARRIER_ID = R.CARRIER_ID   WHERE ISNULL(O.CARRIER_ID,'') <> '' AND  R.ORDER_ID = @ORDER_ID  GROUP BY R.ORDER_ID,R.EXTERN_ORDER_ID,R.OWNER_ID,O.CARRIER_ID,OD.SKU_ID,OD.OWNER_ID,O.COMPANY ";
            }
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            List<BillingOrderDetailInfo> billingOrderDetailListInfo = new List<BillingOrderDetailInfo>();
            BillingOrderDetailInfo billingOrderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    billingOrderDetailInfo = new BillingOrderDetailInfo(reader["DOC_ID"].ToString(), reader["EXTERNAL_DOC_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["BILLING_OBJECT"].ToString(), reader["BILLING_OBJECT_ID"].ToString(), reader["BILLING_OBJECT_NAME"].ToString(), reader["BILLING_TYPE"].ToString(), reader["SKU_ID"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), decimal.Parse((reader["CASE"].ToString() == "") ? "0" : reader["CASE"].ToString()), decimal.Parse((reader["PALLET"].ToString() == "") ? "0" : reader["PALLET"].ToString()));
                    billingOrderDetailListInfo.Add(billingOrderDetailInfo);
                }
            }
            return billingOrderDetailListInfo;
        }

        public OrderDetailInfo GetOrderDetailByID(string orderID, string lineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID AND OD.LINE_ID = @LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrderDetailInfo orderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), 0M, reader["VENDOR_ID"].ToString(), reader["INTRANSIT_ID"].ToString(), 0M, reader["STATUS_D"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, (reader["NUMBER_LOST"].ToString() != "") ? decimal.Parse(reader["NUMBER_LOST"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, reader["BATCH_FLAG"].ToString(), reader["SKU_STYLE"].ToString());
                }
            }
            return orderDetailInfo;
        }

        public OrderDetailInfo GetOrderDetailByID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID AND OD.LINE_ID = @LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrderDetailInfo orderDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), 0M, reader["VENDOR_ID"].ToString(), reader["INTRANSIT_ID"].ToString(), 0M, reader["STATUS_D"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, (reader["NUMBER_LOST"].ToString() != "") ? decimal.Parse(reader["NUMBER_LOST"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, reader["BATCH_FLAG"].ToString(), reader["SKU_STYLE"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return orderDetailInfo;
        }

        public DataSet GetOrderDetailByPagerQueryList(List<string> sqlWhere, OrderDetailQueryEntity orderDetailQueryEntity, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID ";
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                temp = temp + " AND " + sqlWhere[i].ToString();
            }
            if (!orderDetailQueryEntity.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, orderDetailQueryEntity.CurrentPage, orderDetailQueryEntity.PageSize, orderDetailQueryEntity.SortField, orderDetailQueryEntity.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''").Replace("@ORDER_ID", "''" + orderDetailQueryEntity.OrderID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'").Replace("@ORDER_ID", "''" + orderDetailQueryEntity.OrderID + "''");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetOrderDetailByQueryList(List<string> sqlWhere, OrderDetailQueryEntity orderdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!orderdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, orderdetailQuery.CurrentPage, orderdetailQuery.PageSize, orderdetailQuery.SortField, orderdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetOrderDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, OrderDetailQueryEntity orderdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!orderdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, orderdetailQuery.CurrentPage, orderdetailQuery.PageSize, orderdetailQuery.SortField, orderdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<OrderDetailInfo> GetOrderDetailInfoByWaveTemplate(List<string> sqlWhere, string MaxOrders, string whLoginID, PubParasInfo pubParasInfo)
        {
            string strSql = this.SQL_SELECT_ALL_ORDER_DETAILA_WAVE_TEMPLATE.Replace("@_@", whLoginID);
            if (!(string.IsNullOrEmpty(MaxOrders) || !(MaxOrders != "0")))
            {
                strSql = strSql.Replace("@MAX_ORDERS", "top " + MaxOrders);
            }
            else
            {
                strSql = strSql.Replace("@MAX_ORDERS", "");
            }
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    strSql = strSql + " WHERE " + sqlWhere[i];
                }
                else if (!(sqlWhere[i].StartsWith("AND") || sqlWhere[i].StartsWith("OR")))
                {
                    strSql = strSql + " AND " + sqlWhere[i];
                }
            }
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql, paras))
            {
                while (reader.Read())
                {
                    orderDetailInfo = new OrderDetailInfo(reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["CARRIER_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["STATUS"].ToString());
                    orderDetailInfos.Add(orderDetailInfo);
                }
            }
            return orderDetailInfos;
        }

        public List<OrderDetailInfo> GetOrderDetailListByIDAndOrigLineID(DataBase dataBase, DbTransaction tran, string orderID, string origLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID  AND ORIGINAL_LINE_ID = @ORIGINAL_LINE_ID  ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORIGINAL_LINE_ID", origLineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), 0M, reader["VENDOR_ID"].ToString(), "", 0M, reader["STATUS_D"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, (reader["NUMBER_LOST"].ToString() != "") ? decimal.Parse(reader["NUMBER_LOST"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, reader["BATCH_FLAG"].ToString(), reader["SKU_STYLE"].ToString());
                orderDetailInfos.Add(orderDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return orderDetailInfos;
        }

        public List<OrderDetailInfo> GetOrderDetailListByOrderID(string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), 0M, reader["VENDOR_ID"].ToString(), "", 0M, reader["STATUS_D"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, (reader["NUMBER_LOST"].ToString() != "") ? decimal.Parse(reader["NUMBER_LOST"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, reader["BATCH_FLAG"].ToString(), reader["SKU_STYLE"].ToString());
                    orderDetailInfos.Add(orderDetailInfo);
                }
            }
            return orderDetailInfos;
        }

        public List<OrderDetailInfo> GetOrderDetailListByOrderID(string orderID, string where, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID " + where + "  ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), 0M, reader["VENDOR_ID"].ToString(), "", 0M, reader["STATUS_D"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, (reader["NUMBER_LOST"].ToString() != "") ? decimal.Parse(reader["NUMBER_LOST"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, reader["BATCH_FLAG"].ToString(), reader["SKU_STYLE"].ToString());
                    orderDetailInfos.Add(orderDetailInfo);
                }
            }
            return orderDetailInfos;
        }

        public List<OrderDetailInfo> GetOrderDetailListByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID  ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            using (IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), 0M, reader["VENDOR_ID"].ToString(), "", 0M, reader["STATUS_D"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, (reader["NUMBER_LOST"].ToString() != "") ? decimal.Parse(reader["NUMBER_LOST"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, reader["BATCH_FLAG"].ToString(), reader["SKU_STYLE"].ToString());
                    orderDetailInfos.Add(orderDetailInfo);
                }
            }
            return orderDetailInfos;
        }

        public List<OrderDetailInfo> GetOrderDetailListByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string where, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID  " + where + "  ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), 0M, reader["VENDOR_ID"].ToString(), "", 0M, reader["STATUS_D"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, (reader["NUMBER_LOST"].ToString() != "") ? decimal.Parse(reader["NUMBER_LOST"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, reader["BATCH_FLAG"].ToString(), reader["SKU_STYLE"].ToString());
                orderDetailInfos.Add(orderDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return orderDetailInfos;
        }

        public List<OrderDetailInfo> GetOrderDetailListByOrderIDAndContainerID(string orderID, string ContainerID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA_FOR_CONTAINER.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID ORDER BY LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@CONTAINER_ID", ContainerID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), Convert.ToDecimal(reader["ENCASED_QTY"]), reader["VENDOR_ID"].ToString(), "", Convert.ToDecimal(reader["UNENCASED_QTY"]), reader["STATUS_D"].ToString(), (reader["DECLARATION_QTY"].ToString() != "") ? decimal.Parse(reader["DECLARATION_QTY"].ToString()) : 0M, (reader["NUMBER_LOST"].ToString() != "") ? decimal.Parse(reader["NUMBER_LOST"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, reader["BATCH_FLAG"].ToString(), reader["SKU_STYLE"].ToString());
                    orderDetailInfos.Add(orderDetailInfo);
                }
            }
            return orderDetailInfos;
        }

        public List<string> GetOrderLineIDByOrderID4Shipping(string orderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_GET_ORDER_LINE_ID_FOR_SHIPPING.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            List<string> orderLineIDs = new List<string>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    orderLineIDs.Add(reader["LINE_ID"].ToString());
                }
            }
            return orderLineIDs;
        }

        public decimal GetPreAllocatedQtyByLineIDAndOrigLineID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string whLoginID)
        {
            string sql = "SELECT SUM(PREALLOCATED_QTY) FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID AND (LINE_ID = @LINE_ID OR ORIGINAL_LINE_ID = @LINE_ID)".Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
            object obj = dataBase.ExecuteScalar(tran, CommandType.Text, sql, paras);
            if (obj != null)
            {
                return decimal.Parse(obj.ToString());
            }
            return 0M;
        }

        public List<string> GetTotalQTYPriceByOrderID(string orderID, string whLoginID)
        {
            string sql = "SELECT ORDER_ID,SUM(ORIGINAL_QTY) AS ORIGINAL_QTY,SUM(PREALLOCATED_QTY) AS PREALLOCATED_QTY,SUM(ALLOCATED_QTY) AS ALLOCATED_QTY,SUM(PICKED_QTY) AS PICKED_QTY,SUM(SHIPPED_QTY) AS SHIPPED_QTY,SUM(EXTENDED_PRICE) AS EXTENDED_PRICE,SUM(CUBE) AS TOTAL_CUBE,SUM(NET_WGT) AS TOTAL_NET_WGT,SUM(GROSS_WGT) AS TOTAL_GROSS_WGT FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID GROUP BY ORDER_ID";
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            List<string> total = new List<string>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    total.Add(reader["ORIGINAL_QTY"].ToString());
                    total.Add(reader["PREALLOCATED_QTY"].ToString());
                    total.Add(reader["ALLOCATED_QTY"].ToString());
                    total.Add(reader["PICKED_QTY"].ToString());
                    total.Add(reader["SHIPPED_QTY"].ToString());
                    total.Add(reader["EXTENDED_PRICE"].ToString());
                    total.Add(reader["TOTAL_CUBE"].ToString());
                    total.Add(reader["TOTAL_NET_WGT"].ToString());
                    total.Add(reader["TOTAL_GROSS_WGT"].ToString());
                }
            }
            return total;
        }

        public int InsertOrderDetail(OrderDetailInfo orderDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                orderDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OrderDetail_Parameters(orderDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ORDER_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertOrderDetail(DataBase dataBase, DbTransaction tran, OrderDetailInfo orderDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                orderDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OrderDetail_Parameters(orderDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ORDER_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int IsCopyOrderDetail(DataBase dataBase, DbTransaction tran, string newOrderID, string oldOrderID, string userID, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = this.SQL_IS_COPY.Replace("@_@", whLoginID).Replace("@ORDER_ID", newOrderID).Replace("@OLD_ORDER_ID", oldOrderID).Replace("@USER_ID", userID);
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public bool RevertOrderDetailAllocate(PickDetailInfo pickDetailInfo, string whLoginID, PubParasInfo pubParasInfo)
        {
            try
            {
                string sql = string.Empty;
                if (!string.IsNullOrEmpty(pickDetailInfo.PreallocatePickDetailID.Trim()))
                {
                    sql = "UPDATE PPD SET PPD.QTY = PPD.QTY + @QTY,PPD.UPDATED_BY = @UPDATED_BY,PPD.UPDATED_DATE = GETDATE()  FROM  @_@PREALLOCATE_PICK_DETAIL PPD INNER JOIN @_@PICK_DETAIL PD ON PPD.PREALLOCATE_PICK_DETAIL_ID = PD.PREALLOCATE_PICK_DETAIL_ID WHERE PD.PICK_DETAIL_ID = @PICK_DETAIL_ID; UPDATE @_@ORDER_DETAIL SET PREALLOCATED_QTY = PREALLOCATED_QTY + @QTY, ALLOCATED_QTY = ALLOCATED_QTY - @QTY ,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID; UPDATE @_@INV_LOT SET PREALLOCATED_QTY = PREALLOCATED_QTY + @QTY,ALLOCATED_QTY = ALLOCATED_QTY - @QTY,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE LOT = @LOT; UPDATE @_@INV_LOT_LOC_LPN SET ALLOCATED_QTY = ALLOCATED_QTY - @QTY,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN; UPDATE @_@INV_SKU_LOC SET ALLOCATED_QTY = ALLOCATED_QTY - @QTY,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC; DELETE FROM @_@PICK_DETAIL WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID;";
                }
                else
                {
                    sql = "UPDATE @_@ORDER_DETAIL SET ALLOCATED_QTY = ALLOCATED_QTY - @QTY,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID; UPDATE @_@INV_LOT SET ALLOCATED_QTY = ALLOCATED_QTY - @QTY,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE LOT = @LOT; UPDATE @_@INV_LOT_LOC_LPN SET ALLOCATED_QTY = ALLOCATED_QTY - @QTY,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE LOT = @LOT AND LOC = @LOC AND LPN_ID = @LPN; UPDATE @_@INV_SKU_LOC SET ALLOCATED_QTY = ALLOCATED_QTY - @QTY,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC; DELETE FROM @_@PICK_DETAIL WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID;";
                }
                sql = sql.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", pickDetailInfo.OrderID), new SqlParameter("@LINE_ID", pickDetailInfo.OrderLineID), new SqlParameter("@PICK_DETAIL_ID", pickDetailInfo.PickDetailID), new SqlParameter("@QTY", pickDetailInfo.Qty), new SqlParameter("@OWNER_ID", pickDetailInfo.OwnerID), new SqlParameter("@SKU_ID", pickDetailInfo.SkuID), new SqlParameter("@LOT", pickDetailInfo.Lot), new SqlParameter("@LOC", pickDetailInfo.Loc), new SqlParameter("@LPN", pickDetailInfo.LpnID), new SqlParameter("@UPDATED_BY", pubParasInfo.UserID) };
                return (DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) > 0);
            }
            catch (Exception)
            {
                return false;
            }
        }

        public decimal RevertOrderDetailPreAllocate(DataBase dataBase, DbTransaction tran, string orderID, string orderLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            decimal revertQty = 0M;
            string sql = "SELECT SUM(QTY) AS REVERT_QTY FROM @_@PREALLOCATE_PICK_DETAIL WHERE ORDER_ID = @ORDER_ID AND ORDER_LINE_ID = @ORDER_LINE_ID ; UPDATE IL SET IL.PREALLOCATED_QTY = IL.PREALLOCATED_QTY - PPD.QTY,IL.UPDATED_BY = @UPDATED_BY,IL.UPDATED_DATE = GETDATE()  FROM @_@INV_LOT IL INNER JOIN (SELECT LOT,SUM(QTY) AS QTY FROM @_@PREALLOCATE_PICK_DETAIL WHERE ORDER_ID = @ORDER_ID AND ORDER_LINE_ID = @ORDER_LINE_ID GROUP BY LOT) PPD  ON IL.LOT = PPD.LOT; UPDATE OD SET OD.PREALLOCATED_QTY = OD.PREALLOCATED_QTY - PPD.QTY,OD.UPDATED_BY = @UPDATED_BY,OD.UPDATED_DATE = GETDATE() FROM @_@ORDER_DETAIL OD INNER JOIN (SELECT SUM(QTY) AS QTY,ORDER_ID,ORDER_LINE_ID FROM @_@PREALLOCATE_PICK_DETAIL WHERE ORDER_ID = @ORDER_ID AND ORDER_LINE_ID = @ORDER_LINE_ID GROUP BY ORDER_ID,ORDER_LINE_ID) PPD ON OD.ORDER_ID = PPD.ORDER_ID AND OD.LINE_ID = PPD.ORDER_LINE_ID WHERE OD.ORDER_ID = @ORDER_ID AND OD.LINE_ID = @ORDER_LINE_ID; DELETE PPD FROM @_@PREALLOCATE_PICK_DETAIL PPD WHERE PPD.ORDER_ID = @ORDER_ID AND PPD.ORDER_LINE_ID = @ORDER_LINE_ID AND PPD.PREALLOCATE_PICK_DETAIL_ID NOT IN ( SELECT PREALLOCATE_PICK_DETAIL_ID FROM @_@PICK_DETAIL WHERE ORDER_ID = @ORDER_ID AND ORDER_LINE_ID = @ORDER_LINE_ID) ";
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", orderLineID), new SqlParameter("@UPDATED_BY", pubParasInfo.UserID) };
            DataSet ds = new DataSet();
            ds = dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
            if (((ds == null) || (ds.Tables.Count <= 0)) || (ds.Tables[0].Rows.Count <= 0))
            {
                return revertQty;
            }
            try
            {
                return decimal.Parse(string.IsNullOrEmpty(ds.Tables[0].Rows[0][0].ToString()) ? "0" : ds.Tables[0].Rows[0][0].ToString());
            }
            catch
            {
                return 0M;
            }
        }

        private SqlParameter[] Set_OrderDetail_Parameters(OrderDetailInfo orderDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.EXTERN_ORDER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.EXTERN_LINE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.ALT_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORIGINAL_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.OPEN_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.SHIPPED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ADJUSTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PREALLOCATED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ALLOCATED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PICKED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), 
                new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.PICK_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.CARTON_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.UNIT_PRICE, SqlDbType.Float, 8), new SqlParameter(this.TAX01, SqlDbType.Float, 8), new SqlParameter(this.TAX02, SqlDbType.Float, 8), new SqlParameter(this.EXTENDED_PRICE, SqlDbType.Float, 8), new SqlParameter(this.UPDATE_SOURCE, SqlDbType.VarChar, 20), new SqlParameter(this.PRODUCT_WEIGHT, SqlDbType.Float, 8), new SqlParameter(this.PRODUCT_CUBE, SqlDbType.Float, 8), new SqlParameter(this.CARTON_BREAK, SqlDbType.VarChar, 10), new SqlParameter(this.CARTON_QTY_BREAK, SqlDbType.Int, 4), new SqlParameter(this.QC_REQUIRED, SqlDbType.VarChar, 1), new SqlParameter(this.QC_AUTO_ADJUST, SqlDbType.VarChar, 1), 
                new SqlParameter(this.SHIP_GROUP01, SqlDbType.VarChar, 1), new SqlParameter(this.SHIP_GROUP02, SqlDbType.VarChar, 1), new SqlParameter(this.SHIP_GROUP03, SqlDbType.VarChar, 1), new SqlParameter(this.TARIFF_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORIGINAL_LINE_ID, SqlDbType.VarChar, 5), new SqlParameter(this.ORIGINAL_SKU, SqlDbType.VarChar, 50), new SqlParameter(this.BUYER_PO, SqlDbType.VarChar, 20), new SqlParameter(this.ACTUAL_SHIP_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.IS_SUBSTITUTE, SqlDbType.Int, 4), new SqlParameter(this.OK_TO_SUBSTITUTE, SqlDbType.Int, 4), new SqlParameter(this.ALLOCATE_RULE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PREALLOCATE_RULE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ALLOCATE_STRATEGY_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ROTATION, SqlDbType.VarChar, 20), new SqlParameter(this.SHELF_LIFE, SqlDbType.Int, 4), new SqlParameter(this.ROTATION, SqlDbType.VarChar, 1), 
                new SqlParameter(this.SUB_FLAG, SqlDbType.VarChar, 1), new SqlParameter(this.WORK_ORDER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LINE_TYPE, SqlDbType.VarChar, 1), new SqlParameter(this.COMPONENT_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.COMPONENT_REFERENCE, SqlDbType.VarChar, 50), new SqlParameter(this.LOT, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR01, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR02, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR03, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR04, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR05, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR06, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR07, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR08, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR09, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR10, SqlDbType.VarChar, 50), 
                new SqlParameter(this.PRODUCE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPIRY_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXTERNAL_LOT, SqlDbType.VarChar, 100), new SqlParameter(this.EFFECTIVE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.PALLET_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORIG_CASE_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ORIG_PALLET_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.OPP_REQUEST, SqlDbType.VarChar, 1), new SqlParameter(this.PICKING_INSTRUCTIONS, SqlDbType.VarChar, 0xff), new SqlParameter(this.QTY_INTRANSIT, SqlDbType.Decimal, 13), new SqlParameter(this.WP_RELEASED, SqlDbType.VarChar, 1), new SqlParameter(this.EXTERNAL_LOC_SEQUENCE, SqlDbType.VarChar, 20), new SqlParameter(this.MIN_SHIPPER_CENT, SqlDbType.Int, 4), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x3e8), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIPT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.DECLARATION_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.NUMBER_LOST, SqlDbType.Decimal, 13), new SqlParameter(this.RECEIVED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.CUBE, SqlDbType.Float), 
                new SqlParameter(this.GROSS_WGT, SqlDbType.Float), new SqlParameter(this.NET_WGT, SqlDbType.Float), new SqlParameter(this.LENGTH, SqlDbType.Float), new SqlParameter(this.WIDTH, SqlDbType.Float), new SqlParameter(this.HEIGHT, SqlDbType.Float), new SqlParameter(this.BATCH_FLAG, SqlDbType.VarChar, 1)
             };
            if (!string.IsNullOrEmpty(orderDetailInfo.WhID))
            {
                paramArray[0].Value = orderDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = orderDetailInfo.OrderID;
            paramArray[2].Value = orderDetailInfo.LineID;
            paramArray[3].Value = orderDetailInfo.ExternOrderID;
            paramArray[4].Value = orderDetailInfo.ExternLineID;
            paramArray[5].Value = orderDetailInfo.SkuID;
            paramArray[6].Value = orderDetailInfo.AltSkuID;
            paramArray[7].Value = orderDetailInfo.OwnerID;
            paramArray[8].Value = orderDetailInfo.OriginalQty;
            paramArray[9].Value = orderDetailInfo.OpenQty;
            paramArray[10].Value = orderDetailInfo.ShippedQty;
            paramArray[11].Value = orderDetailInfo.AdjustedQty;
            paramArray[12].Value = orderDetailInfo.PreallocatedQty;
            paramArray[13].Value = orderDetailInfo.AllocatedQty;
            paramArray[14].Value = orderDetailInfo.PickedQty;
            paramArray[15].Value = orderDetailInfo.UomID;
            paramArray[0x10].Value = orderDetailInfo.PackID;
            paramArray[0x11].Value = orderDetailInfo.PickCode;
            paramArray[0x12].Value = orderDetailInfo.CartonGroup;
            paramArray[0x13].Value = orderDetailInfo.LpnID;
            paramArray[20].Value = orderDetailInfo.Status;
            paramArray[0x15].Value = orderDetailInfo.UnitPrice;
            paramArray[0x16].Value = orderDetailInfo.Tax01;
            paramArray[0x17].Value = orderDetailInfo.Tax02;
            paramArray[0x18].Value = orderDetailInfo.ExtendedPrice;
            paramArray[0x19].Value = orderDetailInfo.UpdateSource;
            paramArray[0x1a].Value = orderDetailInfo.ProductWeight;
            paramArray[0x1b].Value = orderDetailInfo.ProductCube;
            if (!string.IsNullOrEmpty(orderDetailInfo.CartonBreak))
            {
                paramArray[0x1c].Value = orderDetailInfo.CartonBreak;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            paramArray[0x1d].Value = orderDetailInfo.CartonQtyBreak;
            paramArray[30].Value = orderDetailInfo.QcRequired;
            paramArray[0x1f].Value = orderDetailInfo.QcAutoAdjust;
            paramArray[0x20].Value = orderDetailInfo.ShipGroup01;
            paramArray[0x21].Value = orderDetailInfo.ShipGroup02;
            paramArray[0x22].Value = orderDetailInfo.ShipGroup03;
            if (!string.IsNullOrEmpty(orderDetailInfo.TariffID))
            {
                paramArray[0x23].Value = orderDetailInfo.TariffID;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.OriginalLineID))
            {
                paramArray[0x24].Value = orderDetailInfo.OriginalLineID;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.OriginalSku))
            {
                paramArray[0x25].Value = orderDetailInfo.OriginalSku;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.BuyerPo))
            {
                paramArray[0x26].Value = orderDetailInfo.BuyerPo;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            DateTime CS00003 = new DateTime();
            if (orderDetailInfo.ActualShipDate != CS00003)
            {
                paramArray[0x27].Value = orderDetailInfo.ActualShipDate;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            paramArray[40].Value = orderDetailInfo.IsSubstitute;
            paramArray[0x29].Value = orderDetailInfo.OkToSubstitute;
            if (!string.IsNullOrEmpty(orderDetailInfo.AllocateRuleID))
            {
                paramArray[0x2a].Value = orderDetailInfo.AllocateRuleID;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.PreallocateRuleID))
            {
                paramArray[0x2b].Value = orderDetailInfo.PreallocateRuleID;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.AllocateStrategyType))
            {
                paramArray[0x2c].Value = orderDetailInfo.AllocateStrategyType;
            }
            else
            {
                paramArray[0x2c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.SkuRotation))
            {
                paramArray[0x2d].Value = orderDetailInfo.SkuRotation;
            }
            else
            {
                paramArray[0x2d].Value = DBNull.Value;
            }
            paramArray[0x2e].Value = orderDetailInfo.ShelfLife;
            if (!string.IsNullOrEmpty(orderDetailInfo.Rotation))
            {
                paramArray[0x2f].Value = orderDetailInfo.Rotation;
            }
            else
            {
                paramArray[0x2f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.SubFlag))
            {
                paramArray[0x30].Value = orderDetailInfo.SubFlag;
            }
            else
            {
                paramArray[0x30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.WorkOrderID))
            {
                paramArray[0x31].Value = orderDetailInfo.WorkOrderID;
            }
            else
            {
                paramArray[0x31].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.LineType))
            {
                paramArray[50].Value = orderDetailInfo.LineType;
            }
            else
            {
                paramArray[50].Value = DBNull.Value;
            }
            paramArray[0x33].Value = orderDetailInfo.ComponentQty;
            if (!string.IsNullOrEmpty(orderDetailInfo.ComponentReference))
            {
                paramArray[0x34].Value = orderDetailInfo.ComponentReference;
            }
            else
            {
                paramArray[0x34].Value = DBNull.Value;
            }
            paramArray[0x35].Value = orderDetailInfo.Lot;
            paramArray[0x36].Value = orderDetailInfo.LotAttr01;
            paramArray[0x37].Value = orderDetailInfo.LotAttr02;
            paramArray[0x38].Value = orderDetailInfo.LotAttr03;
            if (!string.IsNullOrEmpty(orderDetailInfo.LotAttr04))
            {
                paramArray[0x39].Value = orderDetailInfo.LotAttr04;
            }
            else
            {
                paramArray[0x39].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.LotAttr05))
            {
                paramArray[0x3a].Value = orderDetailInfo.LotAttr05;
            }
            else
            {
                paramArray[0x3a].Value = DBNull.Value;
            }
            paramArray[0x3b].Value = orderDetailInfo.LotAttr06;
            paramArray[60].Value = orderDetailInfo.LotAttr07;
            paramArray[0x3d].Value = orderDetailInfo.LotAttr08;
            paramArray[0x3e].Value = orderDetailInfo.LotAttr09;
            paramArray[0x3f].Value = orderDetailInfo.LotAttr10;
            CS00003 = new DateTime();
            if (orderDetailInfo.ProduceDate != CS00003)
            {
                paramArray[0x40].Value = orderDetailInfo.ProduceDate;
            }
            else
            {
                paramArray[0x40].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (orderDetailInfo.ExpiryDate != CS00003)
            {
                paramArray[0x41].Value = orderDetailInfo.ExpiryDate;
            }
            else
            {
                paramArray[0x41].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.ExternalLot))
            {
                paramArray[0x42].Value = orderDetailInfo.ExternalLot;
            }
            else
            {
                paramArray[0x42].Value = DBNull.Value;
            }
            paramArray[0x43].Value = orderDetailInfo.EffectiveDate;
            if (!string.IsNullOrEmpty(orderDetailInfo.PalletID))
            {
                paramArray[0x44].Value = orderDetailInfo.PalletID;
            }
            else
            {
                paramArray[0x44].Value = DBNull.Value;
            }
            paramArray[0x45].Value = orderDetailInfo.OrigCaseQty;
            paramArray[70].Value = orderDetailInfo.OrigPalletQty;
            if (!string.IsNullOrEmpty(orderDetailInfo.OppRequest))
            {
                paramArray[0x47].Value = orderDetailInfo.OppRequest;
            }
            else
            {
                paramArray[0x47].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.PickingInstructions))
            {
                paramArray[0x48].Value = orderDetailInfo.PickingInstructions;
            }
            else
            {
                paramArray[0x48].Value = DBNull.Value;
            }
            paramArray[0x49].Value = orderDetailInfo.QtyIntransit;
            paramArray[0x4a].Value = orderDetailInfo.WpReleased;
            if (!string.IsNullOrEmpty(orderDetailInfo.ExternalLocSequence))
            {
                paramArray[0x4b].Value = orderDetailInfo.ExternalLocSequence;
            }
            else
            {
                paramArray[0x4b].Value = DBNull.Value;
            }
            paramArray[0x4c].Value = orderDetailInfo.MinShipperCent;
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf1))
            {
                paramArray[0x4d].Value = orderDetailInfo.Udf1;
            }
            else
            {
                paramArray[0x4d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf2))
            {
                paramArray[0x4e].Value = orderDetailInfo.Udf2;
            }
            else
            {
                paramArray[0x4e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf3))
            {
                paramArray[0x4f].Value = orderDetailInfo.Udf3;
            }
            else
            {
                paramArray[0x4f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf4))
            {
                paramArray[80].Value = orderDetailInfo.Udf4;
            }
            else
            {
                paramArray[80].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf5))
            {
                paramArray[0x51].Value = orderDetailInfo.Udf5;
            }
            else
            {
                paramArray[0x51].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf6))
            {
                paramArray[0x52].Value = orderDetailInfo.Udf6;
            }
            else
            {
                paramArray[0x52].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf7))
            {
                paramArray[0x53].Value = orderDetailInfo.Udf7;
            }
            else
            {
                paramArray[0x53].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf8))
            {
                paramArray[0x54].Value = orderDetailInfo.Udf8;
            }
            else
            {
                paramArray[0x54].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf9))
            {
                paramArray[0x55].Value = orderDetailInfo.Udf9;
            }
            else
            {
                paramArray[0x55].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf10))
            {
                paramArray[0x56].Value = orderDetailInfo.Udf10;
            }
            else
            {
                paramArray[0x56].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Remark))
            {
                paramArray[0x57].Value = orderDetailInfo.Remark;
            }
            else
            {
                paramArray[0x57].Value = DBNull.Value;
            }
            paramArray[0x58].Value = orderDetailInfo.CreatedBy;
            paramArray[0x59].Value = orderDetailInfo.UpdatedBy;
            if (!string.IsNullOrEmpty(orderDetailInfo.VendorID))
            {
                paramArray[90].Value = orderDetailInfo.VendorID;
            }
            else
            {
                paramArray[90].Value = DBNull.Value;
            }
            if (orderDetailInfo.ReceiptDate != new DateTime())
            {
                paramArray[0x5b].Value = orderDetailInfo.ReceiptDate;
            }
            else
            {
                paramArray[0x5b].Value = DBNull.Value;
            }
            paramArray[0x5c].Value = orderDetailInfo.DeclarationQty;
            paramArray[0x5d].Value = orderDetailInfo.NumberLost;
            paramArray[0x5e].Value = orderDetailInfo.ReceivedQty;
            paramArray[0x5f].Value = orderDetailInfo.Cube;
            paramArray[0x60].Value = orderDetailInfo.GrossWGT;
            paramArray[0x61].Value = orderDetailInfo.NetWGT;
            paramArray[0x62].Value = orderDetailInfo.Length;
            paramArray[0x63].Value = orderDetailInfo.Width;
            paramArray[100].Value = orderDetailInfo.Height;
            if (!string.IsNullOrEmpty(orderDetailInfo.BatchFlag))
            {
                paramArray[0x65].Value = orderDetailInfo.BatchFlag;
                return paramArray;
            }
            paramArray[0x65].Value = "N";
            return paramArray;
        }

        public int UpdateOrderDeclarationQtyByOrderIDLineID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, decimal DifferencesQty, string type, string updateBy, string whLoginID)
        {
            int result = 0;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("  UPDATE @_@ORDER_DETAIL ");
                if (type == "DELETE")
                {
                    strSql.Append(" SET DECLARATION_QTY =DECLARATION_QTY- @DECLARATION_QTY,  ");
                }
                else
                {
                    strSql.Append(" SET DECLARATION_QTY = ISNULL(DECLARATION_QTY,0)+ @DECLARATION_QTY,");
                }
                strSql.Append(" UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID=@LINE_ID");
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATION_QTY", DifferencesQty), new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetail(OrderDetailInfo orderDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                orderDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OrderDetail_Parameters(orderDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ORDER_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateOrderDetail(DataBase dataBase, DbTransaction tran, OrderDetailInfo orderDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                orderDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OrderDetail_Parameters(orderDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ORDER_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailActualShipDateByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string ActualShipDate, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@UPDATED_DATE", ActualShipDate), new SqlParameter("@ACTUAL_SHIP_DATE", ActualShipDate), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ACTUAL_SHIP_DATE_BY_ORDER_ID_LINE_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailBatchFlag(DataBase dataBase, DbTransaction tran, string batchFlag, string orderID, string orderLineID, string whLoginID)
        {
            string sql = this.SQL_UPDATE_ORDER_DETAIL_BATCH_FLAG.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BATCH_FLAG", batchFlag), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
        }

        public int UpdateOrderDetailOrderTrackingByID(DataBase dataBase, DbTransaction tran, string orderID, string status, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = string.Empty;
                string CS40001 = status;
                if (CS40001 != null)
                {
                    if (!(CS40001 == "Delivery"))
                    {
                        if (CS40001 == "Lost")
                        {
                            goto Label_0054;
                        }
                        if (CS40001 == "Rejected")
                        {
                            goto Label_005C;
                        }
                        if (CS40001 == "Settlement")
                        {
                            goto Label_0064;
                        }
                    }
                    else
                    {
                        sql = "UPDATE @_@ORDER_DETAIL SET STATUS = @STATUS , RECEIVED_QTY = SHIPPED_QTY WHERE ORDER_ID = @ORDER_ID";
                    }
                }
                goto Label_006C;
            Label_0054:
                sql = "UPDATE @_@ORDER_DETAIL SET STATUS = @STATUS , NUMBER_LOST = SHIPPED_QTY WHERE ORDER_ID = @ORDER_ID";
                goto Label_006C;
            Label_005C:
                sql = "UPDATE @_@ORDER_DETAIL SET STATUS = @STATUS WHERE ORDER_ID = @ORDER_ID";
                goto Label_006C;
            Label_0064:
                sql = "UPDATE @_@ORDER_DETAIL SET STATUS = @STATUS WHERE ORDER_ID = @ORDER_ID";
            Label_006C:
                sql = sql.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@STATUS", status) };
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailPartLostByID(DataBase dataBase, DbTransaction tran, string orderID, List<string> lineID, List<decimal> numberLost, string whLoginID)
        {
            int result = 0;
            string status = "PartLost";
            try
            {
                string sql = "UPDATE @_@ORDER_DETAIL SET STATUS = @STATUS , NUMBER_LOST = @NUMBER_LOST,RECEIVED_QTY = (SHIPPED_QTY-@NUMBER_LOST)  WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID";
                sql = sql.Replace("@_@", whLoginID);
                for (int i = 0; i < lineID.Count; i++)
                {
                    if (numberLost[i] > 0M)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@STATUS", status), new SqlParameter("@NUMBER_LOST", numberLost[i]), new SqlParameter("@LINE_ID", lineID[i]) };
                        result = dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailStatus(string orderID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_ORDER_DETAIL_STATUS_BY_ORDER_ID.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID) };
                if (paras != null)
                {
                    result = DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailStatus(string orderID, string lineID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_ORDER_DETAIL_STATUS_BY_ORDER_ID_AND_LINE_ID.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
                if (paras != null)
                {
                    result = DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailStatus(DataBase dataBase, DbTransaction tran, string orderID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_ORDER_DETAIL_STATUS_BY_ORDER_ID.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailStatusAndQty(string orderID, string orderLineID, string status, decimal qty, string updatedBy, string updateType, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = string.Empty;
                SqlParameter[] paras = null;
                string CS40001 = updateType;
                if (CS40001 != null)
                {
                    if (!(CS40001 == "Status"))
                    {
                        if (CS40001 == "PreAllocatedQty")
                        {
                            goto Label_00DF;
                        }
                        if (CS40001 == "AllocatedQtyOnly")
                        {
                            goto Label_0154;
                        }
                        if (CS40001 == "AllocatedQty")
                        {
                            goto Label_01C9;
                        }
                        if (CS40001 == "PickedQty")
                        {
                            goto Label_023E;
                        }
                        if (CS40001 == "ShippedQty")
                        {
                            goto Label_02B3;
                        }
                    }
                    else
                    {
                        sql = this.SQL_UPDATE_ORDER_DETAIL_STATUS.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                    }
                }
                goto Label_033D;
            Label_00DF:
                sql = this.SQL_UPDATE_ORDER_DETAIL_PREALLOCATE_QTY.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@PREALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                goto Label_033D;
            Label_0154:
                sql = this.SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY_ONLY.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                goto Label_033D;
            Label_01C9:
                sql = this.SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                goto Label_033D;
            Label_023E:
                sql = this.SQL_UPDATE_ORDER_DETAIL_PICKED_QTY.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                goto Label_033D;
            Label_02B3:
                sql = this.SQL_UPDATE_ORDER_DETAIL_SHIPPED_QTY.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID), new SqlParameter("@ACTUAL_SHIP_DATE", DateTime.Now) };
            Label_033D:
                if (paras != null)
                {
                    result = DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailStatusAndQty(DataBase dataBase, DbTransaction tran, string orderID, string orderLineID, string status, decimal qty, string updatedBy, string updateType, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = string.Empty;
                SqlParameter[] paras = null;
                switch (updateType)
                {
                    case "Status":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_STATUS.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "PreAllocatedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_PREALLOCATE_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PREALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "AllocatedQtyOnly":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY_ONLY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "AllocatedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "RevertAllocatedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_REVERT_ALLOCATE_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "PickedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_PICKED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "ShippedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_SHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID), new SqlParameter("@ACTUAL_SHIP_DATE", DateTime.Now) };
                        break;

                    case "UnShippedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_UN_SHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID), new SqlParameter("@ACTUAL_SHIP_DATE", DateTime.Now) };
                        break;

                    case "UnShippedQtyToPicked":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_UN_SHIPPED_QTY_TO_PICKED.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID), new SqlParameter("@ACTUAL_SHIP_DATE", DateTime.Now) };
                        break;
                }
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailUDF(DataBase dataBase, DbTransaction tran, OrderDetailInfo orderDetailinfo, string whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderDetailinfo.OrderID), new SqlParameter("@LINE_ID", orderDetailinfo.LineID), new SqlParameter("@UDF1", orderDetailinfo.Udf1), new SqlParameter("@UDF2", orderDetailinfo.Udf2), new SqlParameter("@REMARK", orderDetailinfo.Remark), new SqlParameter("@UPDATED_BY", pubParasInfo.UserID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ORDER_DETAIL_UDF.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

